package net.kevin.db.service.imp;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import net.kevin.db.mapper.CodeMasterMapper;
import net.kevin.db.mapper.ColumnMapper;
import net.kevin.db.mapper.ForeignkeyMapper;
import net.kevin.db.mapper.IndexMapper;
import net.kevin.db.mapper.MasterMapper;
import net.kevin.db.mapper.TableMapper;
import net.kevin.db.model.CodeMaster;
import net.kevin.db.model.Foreignkey;
import net.kevin.db.model.Index;
import net.kevin.db.model.Master;
import net.kevin.db.model.TabColumn;
import net.kevin.db.model.Table;
import net.kevin.db.service.IOracleService;

//SELECT * FROM ALL_TABLES WHERE OWNER = 'BMS';--查询表
//SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = 'DUAL' AND OWNER = 'BMS';//查询表注释
//SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='EF_FEE' and owner = 'BMS' ORDER BY COLUMN_NAME;//查询表字段
//SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME='EF_FEE' and COLUMN_NAME = ''; 查询字段注释
//SELECT AI.INDEX_NAME,AI.INDEX_TYPE,TO_CHAR(WM_CONCAT(AIC.COLUMN_NAME)) "COLUMN" FROM ALL_INDEXES AI LEFT JOIN ALL_IND_COLUMNS AIC ON AI.INDEX_NAME = AIC.INDEX_NAME WHERE AI.OWNER = 'BMS' AND AI.TABLE_NAME = 'EF_FEE' GROUP BY AI.INDEX_NAME,AI.INDEX_TYPE
@Service
public class OracleServiceImpl implements IOracleService {
	private JdbcTemplate jdbcTemplate;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public Table getTable(String user, String tableName) {
		String sql = "SELECT T.OWNER,T.TABLE_NAME,T.TABLESPACE_NAME,C.COMMENTS FROM ALL_TABLES T LEFT JOIN ALL_TAB_COMMENTS C ON T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME WHERE T.OWNER = ? and T.TABLE_NAME = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { user, tableName }, new TableMapper());
	}

	@Override
	public List<Table> getTable(String user) {
		String sql = "SELECT T.OWNER,T.TABLE_NAME,T.TABLESPACE_NAME,C.COMMENTS FROM ALL_TABLES T LEFT JOIN ALL_TAB_COMMENTS C ON T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME WHERE T.OWNER = ?";
		return jdbcTemplate.query(sql, new Object[] { user }, new TableMapper());
	}

	@Override
	public List<TabColumn> getColumn(String user, String tableName) {
		String sql = "SELECT ATC.DATA_DEFAULT,ATC.OWNER,ATC.TABLE_NAME,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.DATA_PRECISION,ATC.DATA_SCALE,ATC.NULLABLE,ACC.COMMENTS FROM ALL_TAB_COLUMNS ATC LEFT JOIN ALL_COL_COMMENTS ACC ON ATC.OWNER = ACC.OWNER and ATC.TABLE_NAME = ACC.TABLE_NAME AND ATC.COLUMN_NAME = ACC.COLUMN_NAME WHERE ATC.OWNER = ? AND ATC.TABLE_NAME = ? ORDER BY ATC.COLUMN_ID ASC";
		return jdbcTemplate.query(sql, new Object[] { user, tableName }, new ColumnMapper());
	}

	@Override
	public List<Foreignkey> getForeignkey(String user, String tableName) {
		String sql = "SELECT FK.TABLE_NAME,FK.COLUMN_NAME,FK.FOREIGN_TABLE,FK.FOREIGN_COLUMN FROM FOREIGN_KEY FK WHERE FK.OWNER = ? AND FK.TABLE_NAME = ?";
		return jdbcTemplate.query(sql, new Object[] { user, tableName }, new ForeignkeyMapper());
	}

	@Override
	public List<Index> getIndex(String user, String tableName) {
		String sql = "SELECT INDEX_NAME,INDEX_TYPE,TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLUMNS FROM (SELECT AI.INDEX_NAME,AI.INDEX_TYPE,AIC.COLUMN_NAME FROM ALL_INDEXES AI LEFT JOIN ALL_IND_COLUMNS AIC ON AI.INDEX_NAME = AIC.INDEX_NAME WHERE AI.OWNER = ? AND AI.TABLE_NAME = ? ORDER BY AIC.COLUMN_POSITION ASC) GROUP BY INDEX_NAME,INDEX_TYPE";
		return jdbcTemplate.query(sql, new Object[] { user, tableName }, new IndexMapper());
	}

	@Override
	public List<CodeMaster> getCodeMaster(String user, String tableName) {
		String sql = "SELECT CM.TABLE_NAME,CM.COLUMN_NAME,CM.MASTER_CODE,CM.MASTER_NAME FROM COLUMN_MASTER CM WHERE CM.OWNER = ? AND CM.TABLE_NAME = ? ORDER BY ORDER_SORT ASC";
		List<CodeMaster> list = jdbcTemplate.query(sql, new Object[] { user, tableName }, new CodeMasterMapper());
		if (list != null && !list.isEmpty()) {
			for (CodeMaster codeMaster : list) {
				codeMaster.setMaster(getMaster(codeMaster.getMasterCode()));
			}
		}
		return list;
	}

	public List<Master> getMaster(String masterCode) {
		String sql = "select ECM.EBCD_CODE,ECM.EBCD_NAME_CN,DECODE(ECM.USE_STATUS,0,'停用',1,'启用')STATUS from APP.EB_CODE_MASTER ECM where ECM.EBCD_DICTIONARY_TYPE = ? ORDER BY ECM.EBCD_SQUENCE_NO ASC";
		return jdbcTemplate.query(sql, new Object[] { masterCode }, new MasterMapper());
	}
}
